package com.sub.util;

import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFPictureData;
import org.apache.poi.hssf.usermodel.HSSFShape;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFShape;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;

import com.alibaba.druid.util.StringUtils;

/**
 * @Description: TODO(excel工具)
 * @author wangkai
 * @date 2019年1月29日
 *
 */
public class ExcelUtil {

	/**
	 * 获取单元格数据
	 * 
	 * @param cell
	 * @return
	 */
	public static String getCellValueByCell(Cell cell) {
		// 判断是否为null或空串
		if (cell == null || cell.toString().trim().equals("")) {
			return "";
		}
		String cellValue = "";
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_STRING: // 字符串类型
			if (cell.getStringCellValue() != null) {
				cellValue = cell.getStringCellValue().trim();
				cellValue = StringUtils.isEmpty(cellValue) ? "" : cellValue;
			}
			break;
		case Cell.CELL_TYPE_BOOLEAN: // 布尔类型
			cellValue = String.valueOf(cell.getBooleanCellValue());
			break;
		case Cell.CELL_TYPE_FORMULA: // 公式
			cellValue = cell.getCellFormula() + "";
			break;
		case Cell.CELL_TYPE_NUMERIC: // 数值类型
			if (HSSFDateUtil.isCellDateFormatted(cell)) { // 判断日期类型
				SimpleDateFormat sdp = new SimpleDateFormat("yyyy-MM-dd");
				if (cell.getDateCellValue() != null) {
					cellValue = sdp.format(cell.getDateCellValue());
				}
			} else { // 否
				cellValue = new DecimalFormat("#.######").format(cell.getNumericCellValue());
			}
			break;
		default: // 其它类型，取空串吧
			cellValue = "";
			break;
		}
		return cellValue;
	}

	/**
	 * 获取excel中图片
	 * //03的方法获取图片
	 * @param file
	 * @return
	 * @throws InvalidFormatException
	 * @throws IOException
	 */
	public static Map<String, ExcelImgData> getSheetPictrues03Map(int sheetNum, HSSFSheet sheet, HSSFWorkbook workbook) {
		Map<String, ExcelImgData> result = new HashMap<String, ExcelImgData>();
		List<HSSFPictureData> pictures = workbook.getAllPictures();
		if (pictures.size() != 0) {
			for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {
				HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
				if (shape instanceof HSSFPicture) {
					ExcelImgData sheetIndexPicMap = new ExcelImgData();
					HSSFPicture pic = (HSSFPicture) shape;
					int pictureIndex = pic.getPictureIndex() - 1;
					HSSFPictureData picData = pictures.get(pictureIndex);
					sheetIndexPicMap.setRow(anchor.getRow2());
					sheetIndexPicMap.setCol(anchor.getCol2());
					sheetIndexPicMap.setData(picData);
					result.put(anchor.getRow2() + "", sheetIndexPicMap);
				}
			}
		}
		return result;
	}

	/**
	 * 07的方法获取图片
	 * @param sheetNum
	 * @param sheet
	 * @param workbook
	 * @return
	 */
	public static Map<String, ExcelImgData> getSheetPictrues07Map(int sheetNum, XSSFSheet sheet, XSSFWorkbook workbook) {
		Map<String, ExcelImgData> result = new HashMap<String, ExcelImgData>();
		for (POIXMLDocumentPart dr : sheet.getRelations()) {
			if (dr instanceof XSSFDrawing) {
				XSSFDrawing drawing = (XSSFDrawing) dr;
				List<XSSFShape> shapes = drawing.getShapes();
				for (XSSFShape shape : shapes) {
					ExcelImgData sheetIndexPicMap = new ExcelImgData();
					XSSFPicture pic = (XSSFPicture) shape;
					XSSFClientAnchor anchor = pic.getPreferredSize();
					CTMarker ctMarker = anchor.getFrom();
					sheetIndexPicMap.setRow(ctMarker.getRow());
					sheetIndexPicMap.setCol(ctMarker.getCol());
					sheetIndexPicMap.setData(pic.getPictureData());
					result.put(ctMarker.getRow() + "", sheetIndexPicMap);
				}
			}
		}
		return result;
	}
}

class ExcelImgData {

	private int row;
	private int col;
	private PictureData data;

	public int getRow() {
		return row;
	}

	public int getCol() {
		return col;
	}

	public PictureData getData() {
		return data;
	}

	public void setRow(int row) {
		this.row = row;
	}

	public void setCol(int col) {
		this.col = col;
	}

	public void setData(PictureData data) {
		this.data = data;
	}
}
